package com.company.project.common.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

/**
 * excel导出公共类
 *
 * @author ZYF
 *
 */
@Slf4j
public class ExcelUtils {
    // excel默认宽度；
    private static int width = 256 * 14;
    // 默认字体
    private static String excelfont = "微软雅黑";

    /**
     *
     * @param excelName   导出的EXCEL名字
     * @param sheetName   导出的SHEET名字 当前sheet数目只为1
     * @param headers     导出的表格的表头
     * @param columnNames 导出的数据 map.get(key) 对应的 key
     * @param ds_format   导出数据的样式 1:String left; 2:String center 3:String right 4
     *                    int right 5:float ###,###.## right 6:number: #.00% 百分比
     *                    right
     * @param widths      表格的列宽度 默认为 256*14
     * @param data        数据集 List<Map>
     * @param response
     * @throws IOException
     */
    public static void export(String excelName, String sheetName, String[] headers, String[] columnNames,
                              String[] sumColumns, int[] ds_format, int[] widths, List<Map<String, Object>> data,
                              HttpServletRequest request, HttpServletResponse response) throws IOException {
        if (widths == null) {
            widths = new int[columnNames.length];
            for (int i = 0; i < columnNames.length; i++) {
                widths[i] = width;
            }
        }
        if (ds_format == null) {
            ds_format = new int[columnNames.length];
            for (int i = 0; i < columnNames.length; i++) {
                ds_format[i] = 1;
            }
        }
        // 创建一个工作薄
        XSSFWorkbook wb = new XSSFWorkbook();
        // 创建一个sheet
        XSSFSheet sheet = wb.createSheet("sheet1");
        XSSFCellStyle textStyle = wb.createCellStyle();
        XSSFDataFormat format = wb.createDataFormat();
        textStyle.setDataFormat(format.getFormat("@"));
        // 创建表头，如果没有跳过
        int headerrow = 0;
        if (headers != null) {
            XSSFRow row = sheet.createRow(headerrow);
            // 表头样式
            XSSFCellStyle style = wb.createCellStyle();
            XSSFFont font = wb.createFont();
//		    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            font.setFontName(excelfont);
            font.setFontHeightInPoints((short) 11);
            style.setFont(font);
            style.setAlignment(HorizontalAlignment.CENTER);//居中对齐
            style.setWrapText(true); //自动换行
//		    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//		    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//		    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//		    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//		    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            for (int i = 0; i < headers.length; i++) {
                sheet.setDefaultColumnStyle(i, textStyle);
                sheet.setColumnWidth((short) i, (short) widths[i]);
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(style);
                cell.setCellType(CellType.STRING);
            }
            headerrow++;
        }
        // 表格主体 解析list
        if (data != null) {
            List<XSSFCellStyle> styleList = new ArrayList<XSSFCellStyle>();

            double[] totalResult = new double[columnNames.length];

            for (int i = 0; i < columnNames.length; i++) { // 列数
                XSSFCellStyle style = wb.createCellStyle();
                XSSFFont font = wb.createFont();
                font.setFontName(excelfont);
                font.setFontHeightInPoints((short) 10);
                style.setFont(font);
                style.setAlignment(HorizontalAlignment.CENTER);//居中对齐
                style.setWrapText(true); //自动换行
//		        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//		        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//		        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//		        style.setBorderTop(HSSFCellStyle.BORDER_THIN);

//		        else if(ds_format[i]==2){
//		            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//		        }else if(ds_format[i]==3){
//		            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//		             //int类型
//		        }else if(ds_format[i]==4){
//		            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//		             //int类型
//		            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
//		        }else if(ds_format[i]==5){
//		            //float类型
//		            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//		            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
//		        }else if(ds_format[i]==6){
//		            //百分比类型
//		            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//		            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
//		        }
                styleList.add(style);
            }
            // 数据
            for (int i = 0; i < data.size(); i++) { // 行数
                XSSFRow row = sheet.createRow(headerrow);
                Map<String, Object> map = data.get(i);
                for (int j = 0; j < columnNames.length; j++) { // 列数
                    XSSFCell cell = row.createCell(j);
                    cell.setCellType(CellType.STRING);
                    Object o = map.get(columnNames[j]);
                    if (o == null || "".equals(o)) {
                        if(j == 0) {
                            cell.setCellValue((i+1));
                        }
                        else {
                            cell.setCellValue("");
                        }
                    } else if (ds_format[j] == 4) {
                        // int
                        cell.setCellValue((Long.valueOf((map.get(columnNames[j])) + "")).longValue());
                    } else if (ds_format[j] == 5 || ds_format[j] == 6) {
                        // float
                        cell.setCellValue((Double.valueOf((map.get(columnNames[j])) + "")).doubleValue());
                    } else {
                        cell.setCellValue(map.get(columnNames[j]) + "");
                    }

                    cell.setCellStyle((XSSFCellStyle) styleList.get(j));
                    if (o instanceof Integer) {
                        if (StringUtils.isNotBlank(sumColumns[j])) {
                            totalResult[j] += (int) map.get(columnNames[j]);
                        }
                    } else if (o instanceof Double || o instanceof Float) {
                        if (StringUtils.isNotBlank(sumColumns[j])) {
                            totalResult[j] += (double) map.get(columnNames[j]);
                        }
                    } else if (o instanceof BigDecimal) {
                        if (StringUtils.isNotBlank(sumColumns[j])) {
                            totalResult[j] += ((BigDecimal) map.get(columnNames[j])).doubleValue();
                        }
                    }
                }
                headerrow++;
            }
            // 合计
            XSSFRow row = sheet.createRow(headerrow);
            for (int j = 0; j < totalResult.length; j++) { // 列数
                XSSFCell cell = row.createCell(j);
                if (j == 0) {
                    cell.setCellValue(sumColumns[j]);
                }
                if (StringUtils.isNotBlank(sumColumns[j]) && totalResult[j] != 0) {
                    cell.setCellValue(totalResult[j]);
                }
                cell.setCellStyle((XSSFCellStyle) styleList.get(j));
                cell.setCellType(CellType.STRING);
            }
        }
        // 设置文件名
        String filename = "";
        try {
            filename = encodeChineseDownloadFileName(request, excelName);
        } catch (Exception e) {
            e.printStackTrace();
        }

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=" + filename);
        response.setHeader("Cache-control", "no-cache");
        OutputStream ouputStream = response.getOutputStream();
        wb.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
    }

    /**
     * 对文件流输出下载的中文文件名进行编码 屏蔽各种浏览器版本的差异性
     *
     * @throws UnsupportedEncodingException
     */
    public static String encodeChineseDownloadFileName(HttpServletRequest request, String pFileName) throws Exception {
        String filename = null;
        String agent = request.getHeader("USER-AGENT");
        if (null != agent) {
            if (-1 != agent.indexOf("Firefox")) {// Firefox
                filename = "=?UTF-8?B?"
                        + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8"))))
                        + "?=";
            } else if (-1 != agent.indexOf("Chrome")) {// Chrome
                filename = new String(pFileName.getBytes(), "ISO8859-1");
            } else {// IE7+
                filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
                filename = filename.replace("+", "%20");
            }
        } else {
            filename = pFileName;
        }
        return filename;
    }

    /**
     * exec 导入
     * sheet.getLastRowNum() 总行数
     * Row row = sheet.getRow() 每一行
     * row.getCell() 每一列
     * @param file
     * @return
     * @throws Exception
     */
    public static Sheet excelImport(MultipartFile file) throws Exception {
        //获取Excel格式
        int type = readExcel(file.getOriginalFilename());
        Workbook wb = null;
        //获取第一个工作表的数据（其他工作表数据不操作）
        Sheet sheet = null;

        if (type == 1) {//xls格式
            wb = new HSSFWorkbook(file.getInputStream());
        } else if (type == 2) {//xlsx格式
            wb = new XSSFWorkbook(file.getInputStream());
        } else {
            log.error("请导入正确文件类型");
            return null;
        }

        if (wb != null && wb.getNumberOfSheets() > 0) {
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                if (wb.getSheetAt(i).getPhysicalNumberOfRows() > 0) {
                    sheet = wb.getSheetAt(i);
                    break;
                }
            }
        } else {
            log.error("导入失败，无sheet");
            return null;
        }
        return sheet;
    }
    /**
     * 读取excel格式
     * @param fileName
     * @return
     * @throws Exception
     */
    private static int readExcel(String fileName) {
        String extString = fileName.substring(fileName.lastIndexOf("."));
        try {
            if (".xls".equals(extString)) {
                return 1;
            }
            else if(".xlsx".equals(extString)) {
                return 2;
            }
            else {
                return 0;
            }
        } catch (Exception e) {
            log.info("读取Excel文件异常");
          return 0;
        }
    }
}